package dapps.web.proj;

import java.io.ByteArrayOutputStream;
import java.io.IOException;
import java.io.OutputStream;
import java.text.DecimalFormat;
import java.util.ArrayList;
import java.util.List;

import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;

import org.apache.commons.lang.StringUtils;
import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFCellStyle;
import org.apache.poi.hssf.usermodel.HSSFDataFormat;
import org.apache.poi.hssf.usermodel.HSSFFont;
import org.apache.poi.hssf.usermodel.HSSFFooter;
import org.apache.poi.hssf.usermodel.HSSFPrintSetup;
import org.apache.poi.hssf.usermodel.HSSFRichTextString;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.hssf.util.CellRangeAddress;
import org.hibernate.criterion.Criterion;
import org.hibernate.criterion.MatchMode;
import org.hibernate.criterion.Order;
import org.hibernate.criterion.Restrictions;
import org.springframework.beans.factory.annotation.Autowired;
import org.springside.modules.web.struts2.Struts2Utils;

import com.lowagie.text.BadElementException;
import com.lowagie.text.Cell;
import com.lowagie.text.Document;
import com.lowagie.text.DocumentException;
import com.lowagie.text.Element;
import com.lowagie.text.Font;
import com.lowagie.text.PageSize;
import com.lowagie.text.Paragraph;
import com.lowagie.text.Table;
import com.lowagie.text.pdf.BaseFont;
import com.lowagie.text.rtf.RtfWriter2;
import com.opensymphony.xwork2.ActionSupport;

import dapps.entity.proj.Proj;
import dapps.entity.proj.Type;
import dapps.service.proj.PlanManager;
import dapps.service.proj.ProjManager;
import dapps.service.proj.TypeManager;

@SuppressWarnings("serial")
public class ProjxlsAction extends ActionSupport {

	@Autowired
	private ProjManager projManager;
	@Autowired
	private TypeManager typeManager;
	@Autowired
	private PlanManager planManager;
	
	private HttpServletRequest request = Struts2Utils.getRequest();
	private static String[] headstrarray = { "序号", "重要程度", "项目名称", "建设内容及规模",
			"总投资（万元）", "总用地（亩）", "农用地（亩）", "其中：耕地、林地（亩）",
			"项目建议书批复文号", "项目可研报告(核准或备案)批复文号", "方案设计或初步设计批复文号",
			"规划选址意见书", "规划用地许可证", "用地预审文号", "责任单位", "备注" };
	private static String[] headargarray = { "序号", "重要程度", "项目名称", "建设内容及规模",
		"总投资（万元）", "总用地（亩）", "农用地（亩）", "其中：耕地、林地（亩）",
		"是否已组件上报市国土局", "市国土局是否已收件", "市国土局是否上报省国土资源厅",
		"省国土资源厅是否已批复", "责任单位", "备注" };
	private static String[] title={"实施项目","预备项目","储备项目","拟争取省留指标项目"};
	private static String[] intarray = { "","一", "二", "三","四", "五","六", "七", "八", "九","十"};
	private static String[] intsarray = { "","（一）", "（二）", "（三）","（四）", "（五）","（六）", "（七）", "（八）", "（九）","（十）"};
	private DecimalFormat nfx = new DecimalFormat("0.#");

	@Override
	public String execute() throws Exception {
		return export();
	}

	public String export() throws Exception {
		String[] requestname = {"exporttype","iprorank","iyeartime","detail","tid"};
		int[] requestvalue = new int[requestname.length];
		String tempstr ="";
		for(int i = 0 ;i<requestname.length;i++){
			tempstr = (String)request.getParameter(requestname[i]);
			if (StringUtils.isNotBlank(tempstr)) {
				requestvalue[i] = Integer.parseInt(tempstr);
			}else {
				if(i!=1)requestvalue[i]=0;
				else requestvalue[i]=-1;
			}
		}

		
		List<Criterion> criterionlist = new ArrayList<Criterion>();	
		
		tempstr = (String)request.getParameter("filter_LIKE_title");
		if (StringUtils.isNotBlank(tempstr)) {
			criterionlist.add(Restrictions.like("title", tempstr, MatchMode.ANYWHERE));
		}
		
		tempstr = (String)request.getParameter("filter_LIKE_content");
		if (StringUtils.isNotBlank(tempstr)) {
			criterionlist.add(Restrictions.like("content", tempstr, MatchMode.ANYWHERE));
		}

		
		if(requestvalue[4]>0){
			if(requestvalue[4]%100!=0)
				criterionlist.add(Restrictions.eq("t.tid", requestvalue[4]));
			else{
				criterionlist.add(Restrictions.between("t.tid", requestvalue[4],requestvalue[4]+99));				
			}
		}
		
		Long dutycompid = 0L;
		tempstr = (String)request.getParameter("dutycompid");
		if (StringUtils.isNotBlank(tempstr)) {
			dutycompid = Long.parseLong(tempstr);
			if(dutycompid>0){			
				criterionlist.add(Restrictions.eq("dutycomp.id",dutycompid));
			}
		}
		Long applicateuserid = 0L;
		tempstr = (String)request.getParameter("applicateuserid");
		if (StringUtils.isNotBlank(tempstr)) {
			applicateuserid = Long.parseLong(tempstr);
			if(applicateuserid>0){			
				criterionlist.add(Restrictions.eq("applicateuser.id",applicateuserid));
			}
		}
		

		String[] alias={"type","t","dutycomp","d"};

		Order[] orders={Order.asc("t.tid"),Order.asc("d.uorder"),Order.asc("id")};
		
		List<Proj> dataList = null;
		
		if(requestvalue[0]==0){//excel
			HSSFWorkbook workbook = new HSSFWorkbook();
			if(requestvalue[1]==-1){//项目查询
				criterionlist.add(Restrictions.between("yeartime",requestvalue[2] * 100, (requestvalue[2] + 1) * 100));
				criterionlist.add(Restrictions.ne("prorank",9));
				Boolean checkboxplan = Boolean.valueOf(request.getParameter("checkboxplan"));
				if(!checkboxplan){
					criterionlist.add(
							Restrictions.or(
									Restrictions.and(Restrictions.eq( "prorank", 0 )
													,Restrictions.gt( "yeartime", planManager.getMaxPlaned(requestvalue[2]*100))),
									Restrictions.gt("prorank", 0 )
							)
					 );
				}
				dataList = projManager.FullSearch(null, criterionlist.toArray(new Criterion[criterionlist.size()]), alias, orders);
				if(requestvalue[3]==2)//进度
					this.createAgrSheets(workbook, requestvalue[3], requestvalue[2]/100, null, dataList,0);
				else
					this.createSheets(workbook,requestvalue[3], requestvalue[2]/100,null, dataList,0);
			}else if(requestvalue[1]==4){//多个库
				Criterion[] carray= criterionlist.toArray(new Criterion[criterionlist.size()+2]);
				for(int i=0;i<4;i++){
					carray[criterionlist.size()] = Restrictions.eq("prorank", i);
					if(i==0){
						carray[criterionlist.size()+1]=Restrictions.eq("yeartime", requestvalue[2]);
					}else{
						carray[criterionlist.size()+1]=Restrictions.between("yeartime", requestvalue[2]/100*100,(requestvalue[2]/100+1)*100);
					}
					dataList = projManager.FullSearch(null, carray, alias, orders);
					this.createSheets(workbook,requestvalue[3],requestvalue[2] / 100,  title[i], dataList,i);
				}
				
				
			}else {//单个库
				if (requestvalue[2] > 0) {
					if (requestvalue[2] < 10000) {
						criterionlist.add(Restrictions.between("yeartime",requestvalue[2] * 100, (requestvalue[2] + 1) * 100));
					} else{
						criterionlist.add(Restrictions.eq("yeartime", requestvalue[2]));
					}
				}
				criterionlist.add(Restrictions.eq("prorank", requestvalue[1]));
				dataList = projManager.FullSearch(null, criterionlist.toArray(new Criterion[criterionlist.size()]), alias,orders);
				if(requestvalue[3]==2)//进度
					this.createAgrSheets(workbook, requestvalue[3], requestvalue[2], title[requestvalue[1]], dataList,0);
				else
					this.createSheets(workbook,requestvalue[3], requestvalue[2],title[requestvalue[1]], dataList,0);
			}
			if (workbook != null) {
				this.printExcel(workbook, Struts2Utils.getResponse(), "杭州市"+ requestvalue[2] / 100 + "年市本级新增建设用地项目计划");
			}
		}else if(requestvalue[0]==1){//word
			if(requestvalue[1]==0){
				criterionlist.add(Restrictions.eq("yeartime", requestvalue[2]));
			}else{
				criterionlist.add(Restrictions.between("yeartime", requestvalue[2]/100*100,(requestvalue[2]/100+1)*100));
			}
			criterionlist.add(Restrictions.eq("prorank", requestvalue[1]));
			dataList = projManager.FullSearch(null, criterionlist.toArray(new Criterion[criterionlist.size()]), alias, orders);
			printWord(Struts2Utils.getResponse(),dataList,requestvalue[3],"杭州市"+ requestvalue[2] / 100 + "年市本级新增建设用地项目计划");
		}

		return NONE;
	}

	
	
	// 其他属性访问函数与Action函数 //

	private void printExcel(HSSFWorkbook workbook,
			HttpServletResponse response, String string) throws IOException {
		OutputStream out = response.getOutputStream();
		response.setHeader("Content-disposition", "attachment; filename="
				+ new String(string.getBytes("gb2312"), "ISO8859-1") + ".xls");
		response.setContentType("application/msexcel;charset=UTF-8");
		workbook.write(out);
		out.flush();
		out.close();

	}
	public String printWord(HttpServletResponse response, List<Proj> dataList,int detaili,
			String title) throws Exception {
		Document document = new Document(PageSize.A4.rotate());
		try {
			// 定义输出位置并把文档对象装入输出对象中
			ByteArrayOutputStream outputStream = new ByteArrayOutputStream();
			RtfWriter2.getInstance(document, outputStream);
			// 打开文档对象
			document.open();
			BaseFont bfChinese = BaseFont.createFont("STSongStd-Light","UniGB-UCS2-H", BaseFont.NOT_EMBEDDED);

			Font titleFont = new Font(bfChinese, 22, Font.BOLD);
			Font contextFont = new Font(bfChinese, 10.5f, Font.NORMAL);
			Font tableheadFont = new Font(bfChinese, 10.5f, Font.BOLD);
			Paragraph ptitle = new Paragraph(title);
			ptitle.setAlignment(Element.ALIGN_CENTER);
			ptitle.setFont(titleFont);
			document.add(ptitle);

			// 设置 Table 表格
			Table aTable = new Table(16);
			int width[] = { 3, 3, 8, 8, 5, 5, 5, 5, 7, 7, 7, 7, 7, 7, 6, 5 };
			aTable.setWidths(width);// 设置每列所占比例
			aTable.setWidth(100); // 占页面宽度 90%

			aTable.setAlignment(Element.ALIGN_CENTER);// 居中显示
			aTable.setAlignment(Element.ALIGN_MIDDLE);// 纵向居中显示
			aTable.setAutoFillEmptyCells(true); // 自动填满
			aTable.setBorderWidth(1); // 边框宽度
			aTable.setBorder(2);// 边框

			// 设置表头
			/**
			 * cell.setHeader(true);是将该单元格作为表头信息显示；
			 * cell.setColspan(3);指定了该单元格占3列； 为表格添加表头信息时，要注意的是一旦表头信息添加完了之后， 必须调用
			 * endHeaders()方法，否则当表格跨页后，表头信息不会再显示
			 */

			Cell haderCell = null;			
			
			for (int i = 0; i < headstrarray.length; i++) {
				haderCell = new Cell(new Paragraph(headstrarray[i],tableheadFont));
				if (i < 8 || i > 13) haderCell.setRowspan(2);
				haderCell.setHeader(true);
				haderCell.setHorizontalAlignment(Cell.ALIGN_CENTER);
				haderCell.setVerticalAlignment(Cell.ALIGN_MIDDLE);
				aTable.addCell(haderCell);
				if (i == 7) {
					haderCell = new Cell(new Paragraph("项目前期工作进度",tableheadFont));
					haderCell.setHorizontalAlignment(Cell.ALIGN_CENTER);
					haderCell.setVerticalAlignment(Cell.ALIGN_MIDDLE);
					haderCell.setColspan(6);
					haderCell.setHeader(true);
					i = 13;
					aTable.addCell(haderCell);
				} else if (i == 15) {
					i = 7;
				} else if (i == 13) {
					i = 15;
				}
			}
			aTable.endHeaders();
			int typerow = 0;int typeprow = 0;
			String tempstr = "";String lasttypepname ="";
			if (dataList != null && dataList.size() > 0) {
				
				int lasttid = 0;
				int k = 3;
				
				String lasttypename = "";
				
				Integer sum = new Integer(0);//分类总计
				Integer sump = new Integer(0);//父分类总计
				Integer sumproj = new Integer(0);//总计
				
				Double sumagrarea = new Double(0);Double sumpagrarea = new Double(0);
				Double sumprojagrarea = new Double(0);				
				
				for (int j = 0; j < headstrarray.length; j++) {
					if(j==2){
						Cell cell = new Cell("");
						cell.setColspan(2);
						cell.setHorizontalAlignment(Cell.ALIGN_CENTER);
						aTable.addCell(cell);
						j=j+1;
					}else{						
						aTable.addCell(this.createDocCell(tableheadFont , "c", ""));
					}
					
				}

				for (int i = 0; i < dataList.size(); i++) {
					Proj model = (Proj) dataList.get(i);
					Type modeltype = model.getType();
					if (lasttid != modeltype.getTid()) {
						if (typerow!=0) {// 上一type row									
							insertDocCell(aTable,typerow,2,tableheadFont, lasttypename + "（" + sum + "个项目）");							
							sumproj += sum;sump += sum;
							sum = 0;
							
							insertDocCell(aTable,typerow,6,tableheadFont, nfx.format(sumagrarea));						
							sumprojagrarea += sumagrarea;sumpagrarea += sumagrarea;
							sumagrarea = 0D;

						}
						if(modeltype.getTid()%100==1){  //出现子分类 需要加父分类行
							typeprow = i+k;k++;
							for (int j = 0; j < headstrarray.length; j++) {
								if(j==2){
									Cell cell = new Cell("");
									cell.setColspan(2);
									cell.setHorizontalAlignment(Cell.ALIGN_CENTER);
									aTable.addCell(cell);
									j=j+1;
								}else{
									aTable.addCell(this.createDocCell(tableheadFont , "c", ""));
								}
							}
							sump=0;//sumpinvest=0L;sumparea=0D;
							sumpagrarea=0D;//sumpfarmingarea=0D;
							Type tp = typeManager.getTypeByTid(modeltype.getTid()/100*100);
							lasttypepname = intarray[modeltype.getTid()/100]+ "、" +tp.getName();							
						}
						if(modeltype.getTid()%100==0){ //无子分类时 父分类行赋空 type p row判定是否处在父类下
							if(typeprow!=0){
								insertDocCell(aTable,typeprow,2,tableheadFont,  lasttypepname + "（" + sump + "个项目）");
								insertDocCell(aTable,typeprow,6,tableheadFont, nfx.format(sumpagrarea));
							}
							typeprow = 0;
						}
						
						typerow = i+k;				
						for (int j = 0; j < headstrarray.length; j++) {
							if(j==2){
								Cell cell = new Cell("");
								cell.setColspan(2);
								cell.setHorizontalAlignment(Cell.ALIGN_CENTER);
								aTable.addCell(cell);
								j=j+1;								
							}else{
								aTable.addCell(this.createDocCell(tableheadFont , "c", ""));
							}
							
							
						}
						lasttypename = (modeltype.getTid()%100!=0?intsarray[modeltype.getTid()%100]:intarray[modeltype.getTid() / 100])+ "、" + modeltype.getName();

						k++;
					}

					sumagrarea += (model.getAgrarea() != null) ? model.getAgrarea() : 0;
					sum++;
					
					aTable.addCell(this.createDocCell(contextFont , "c", i + 1));
					aTable.addCell(this.createDocCell(contextFont , "c", (model.getZd() != null && model.getZd() == 1) ? "*": ""));
					aTable.addCell(this.createDocCell(contextFont , "l",  model.getTitle()));
					tempstr = "";
					if (model.getContentlength() != null) tempstr += "长" + model.getContentlength() + "米 ";
					if (model.getContentwidth() != null) tempstr += "，宽" + model.getContentwidth() + "米 ";
					if (model.getContentarea() != null) tempstr += "建筑面积" + model.getContentarea() + "平方米 ";
					if (model.getContent() != null) tempstr += model.getContent();
					aTable.addCell(this.createDocCell(contextFont , "c", tempstr));
					if(model.getType().getId().intValue()==8&&model.getInvest()!=null&&model.getInvest()>0) {
						tempstr = model.getInvest()+"万美元";
						aTable.addCell(this.createDocCell(contextFont , "c", tempstr));
					}else{
						aTable.addCell(this.createDocCell(contextFont , "c",  model.getInvest()));
					}
					aTable.addCell(this.createDocCell(contextFont , "c",  model.getArea()));
					aTable.addCell(this.createDocCell(contextFont , "c",  model.getAgrarea()));
					aTable.addCell(this.createDocCell(contextFont , "c",  model.getFarmingarea()));
					if (detaili == 0) {
						aTable.addCell(this.createDocCell(contextFont , "c",  StringUtils.isNotBlank(model.getJys()) ? "√" : ""));
						aTable.addCell(this.createDocCell(contextFont , "c",  StringUtils.isNotBlank(model.getKy()) ? "√" : ""));
						aTable.addCell(this.createDocCell(contextFont , "c",  StringUtils.isNotBlank(model.getSj()) ? "√" : ""));
						aTable.addCell(this.createDocCell(contextFont , "c",  StringUtils.isNotBlank(model.getXz()) ? "√" : ""));
						aTable.addCell(this.createDocCell(contextFont , "c",  StringUtils.isNotBlank(model.getYdxk()) ? "√" : ""));
						aTable.addCell(this.createDocCell(contextFont , "c",  StringUtils.isNotBlank(model.getYdys()) ? "√" : ""));
					} else {
						aTable.addCell(this.createDocCell(contextFont , "l", model.getJys()));
						aTable.addCell(this.createDocCell(contextFont , "l", model.getKy()));
						aTable.addCell(this.createDocCell(contextFont , "l", model.getSj()));
						aTable.addCell(this.createDocCell(contextFont , "l", model.getXz()));
						aTable.addCell(this.createDocCell(contextFont , "l", model.getYdxk()));
						aTable.addCell(this.createDocCell(contextFont , "l", model.getYdys()));
					}

				aTable.addCell(this.createDocCell(contextFont , "c", model.getDutycomp().getName()));

				tempstr = (model.getBb() != null && model.getBb() == 1) ? "报部": "";
				if (model.getNote() != null) tempstr += model.getNote();
				aTable.addCell(this.createDocCell(contextFont , "c", tempstr));

				lasttid = modeltype.getTid();
			}
			// 最后一个type
			if (typerow != 0) {
				insertDocCell(aTable,typerow,2,tableheadFont, lasttypename + "（" + sum + "个项目）");
				sumproj += sum;			
		
				insertDocCell(aTable,typerow,6,tableheadFont, nfx.format(sumagrarea));						
				sumprojagrarea += sumagrarea;			

			}
			insertDocCell(aTable,2,2,tableheadFont,  "合计（" + sumproj + "个项目）");
			insertDocCell(aTable,2,6,tableheadFont, nfx.format(sumprojagrarea));

		} else {
			this.createDocCell(contextFont , "l",  "查无资料");
		}

			document.add(aTable);
			document.add(new Paragraph("\n")); 
			document.add(new Paragraph(title));
			document.close();
			
			response.addHeader("Content-Disposition","attachment; filename="+ new String(title.getBytes("gb2312"), "ISO8859-1") + ".doc");
			response.setContentType("application/octet-stream");
			response.setContentLength(outputStream.size());
			OutputStream out = response.getOutputStream();
			outputStream.writeTo(out);
			out.flush();
			out.close();
			

		} catch (DocumentException e) {
			e.printStackTrace();
		}
		return NONE;
	}
	
	private Cell createDocCell(Font font,String cellType, Object value) throws BadElementException {
		Cell  cell= new Cell(new Paragraph(value!=null?value.toString():"",font));
		if(cellType.equals("c")){
			cell.setHorizontalAlignment(Cell.ALIGN_CENTER);
		}else{
			cell.setHorizontalAlignment(Cell.ALIGN_LEFT);
		}
		
		cell.setVerticalAlignment(Cell.ALIGN_MIDDLE);
		return cell;
	}
	
	private void insertDocCell(Table table,int x,int y,Font font, Object value) throws BadElementException {		
		Cell c = (Cell) table.getElement(x,y);
		if(c!=null){
			c.addElement(new Paragraph(value!=null?value.toString():"",font));
		}else{
			table.addCell(this.createDocCell(font,"c",value),x,y);
		}
		
	}
	
	public void createSheets(HSSFWorkbook workbook,int detaili,int year, String titlestr,
			List<Proj> dataList,int sheetno) throws Exception {

		HSSFSheet sheet = null;
		try {
			// 这里的数据即时你要从后台取得的数据

			// 创建工作簿实例

			// 创建工作表实例
			sheet = workbook.createSheet(titlestr!=null?titlestr:"项目");
			HSSFPrintSetup hps = sheet.getPrintSetup();
			hps.setPaperSize((short) 9); // 设置A4纸
			hps.setLandscape(true); // 将页面设置为横向打印模式
			hps.setScale((short) 85);
			sheet.setHorizontallyCenter(true); // 设置打印页面为水平居中
			
			HSSFFooter footer = sheet.getFooter();// 设置footer的位置和显示的内容
			footer.setRight("第 " + HSSFFooter.page() + " 页"); 
			sheet.setMargin(HSSFSheet.TopMargin,(double)0.6);  
			sheet.setMargin(HSSFSheet.BottomMargin,(double)0.6);  
			sheet.setMargin(HSSFSheet.LeftMargin,(double)0.6);  
			sheet.setMargin(HSSFSheet.RightMargin,(double)0.6);  
			hps.setHeaderMargin((double)0.5);  
			hps.setFooterMargin((double)0.5);  
			
			// 设置列宽
			this.setSheetColumnWidth(sheet);
			// 获取样式
			HSSFCellStyle titlestyle = this.createTitleStyle(workbook);
			HSSFCellStyle styleC = this.createCommonStyle(workbook,HSSFCellStyle.ALIGN_CENTER);
			HSSFCellStyle styleL = this.createCommonStyle(workbook,HSSFCellStyle.ALIGN_LEFT);
			HSSFCellStyle headstyle = this.createHeadStyle(workbook);
			HSSFCellStyle sumsstyle = this.createSumSStyle(workbook);

			//
			if (dataList != null && dataList.size() > 0) {

				// 建立title行
				HSSFRow rowtitle = sheet.createRow((short) 0);
				String tt ="杭州市";
				if(year>1999&&year<9999) tt+=year+"年";
				else if(year>10000) tt+=year/100+"年第"+intarray[year%100]+"批";
				tt+="市本级新增建设用地项目计划";
				if(titlestr!=null) tt+="（"+titlestr+"）";
				this.createTHCell(rowtitle, 0, titlestyle,tt);

				// 建立head行
				
				HSSFRow headrow1 = sheet.createRow((short) 1);
				HSSFRow headrow2 = sheet.createRow((short) 2);
				for (int i = 0; i < headstrarray.length; i++) {
					if (i >= 8 && i <= 13) {
						this.createTHCell(headrow1, i, headstyle, "项目前期工作进度");
						this.createTHCell(headrow2, i, headstyle,headstrarray[i]);
					} else {
						this.createTHCell(headrow1, i, headstyle,headstrarray[i]);
						this.createTHCell(headrow2, i, headstyle, "");
						sheet.addMergedRegion(new CellRangeAddress(1, 2, i, i));
					}
					sheet.addMergedRegion(new CellRangeAddress(1, 1, 8, 13));

				}
				sheet.addMergedRegion(new CellRangeAddress(0, 0, 0, 15));
				workbook.setRepeatingRowsAndColumns(sheetno,-1,-1,1,2);  

				
				// 给excel填充数据
				int lasttid = 0;
				int k = 4;
				HSSFRow typerow = null;
				HSSFRow typeprow = null;//父类别
				String lasttypename = "";String lasttypepname = "";
				
				Integer sum = new Integer(0);//分类总计
				Integer sump = new Integer(0);//父分类总计
				Integer sumproj = new Integer(0);//总计

				Double sumagrarea = new Double(0);Double sumpagrarea = new Double(0);
				Double sumprojagrarea = new Double(0);				

				// 合计项目个数
				HSSFRow headrow3 = sheet.createRow((short) 3);
				headrow3.setHeight((short) 400);
				String tempstr = "";
				for (int i = 0; i < dataList.size(); i++) {
					Proj model = (Proj) dataList.get(i);
					Type modeltype = model.getType();
					// 分类
					if (lasttid != modeltype.getTid()) {
						if (typerow != null) {// 上一typerow
							typerow.setHeight((short) 700);
							this.createCell(typerow, 2, sumsstyle, "s",lasttypename + "（" + sum + "个项目）");							
							
							sheet.addMergedRegion(new CellRangeAddress(typerow.getRowNum(), typerow.getRowNum(), 2, 3));

							sumproj += sum;
							sump += sum;
							sum = 0;

							typerow.getCell(6).setCellValue(sumagrarea);
							sumprojagrarea += sumagrarea;
							sumpagrarea += sumagrarea;
							sumagrarea = 0D;

						}
						if(modeltype.getTid()%100==1){  //出现子分类 需要加父分类行
							typeprow = sheet.createRow((short) (i + k));k++;
							typeprow.setHeight((short) 700);
							sump=0;
							sumpagrarea=0D;
							Type tp = typeManager.getTypeByTid(modeltype.getTid()/100*100);
							lasttypepname = intarray[modeltype.getTid()/100]+ "、" +tp.getName();							
						}
						if(modeltype.getTid()%100==0){ //无子分类时 父分类行赋空
							if(typeprow!=null){
								this.createCell(typeprow, 2, sumsstyle, "s",lasttypepname + "（" + sump + "个项目）");
								sheet.addMergedRegion(new CellRangeAddress(typeprow.getRowNum(), typeprow.getRowNum(), 2, 3));

								typeprow.getCell(6).setCellValue(sumpagrarea);

							}
							typeprow = null;
						}
						typerow = sheet.createRow((short) (i + k));// 创建此type row
						
						for (int j = 0; j < headstrarray.length; j++) {
							if (i == 0) this.createCell(headrow3, j, sumsstyle, "s",null);// 总合计行各cell
							if (j != 2) this.createCell(typerow, j, sumsstyle, "s",null);// type合计行各cell
							if (modeltype.getTid()%100==1) this.createCell(typeprow, j, sumsstyle, "s",null);
						}
						
						lasttypename = (modeltype.getTid()%100!=0?intsarray[modeltype.getTid()%100]:intarray[modeltype.getTid() / 100])+ "、" + modeltype.getName();

						k++;
					}
					HSSFRow row1 = sheet.createRow((short) (i + k));// 建立新行
					sumagrarea += (model.getAgrarea() != null) ? model.getAgrarea() : 0;
					sum++;

					this.createCell(row1, 0, styleC, "i", i + 1);
					this.createCell(row1, 1, styleC, "s",(model.getZd() != null && model.getZd() == 1) ? "*": "");
					this.createCell(row1, 2, styleL, "s", model.getTitle());					
					tempstr = "";
					if (model.getContentlength() != null) tempstr += "长" + model.getContentlength() + "米 ";
					if (model.getContentwidth() != null) tempstr += "，宽" + model.getContentwidth() + "米 ";
					if (model.getContentarea() != null) tempstr += "建筑面积" + model.getContentarea() + "平方米 ";
					if (model.getContent() != null) tempstr += model.getContent();
					this.createCell(row1, 3, styleL, "s", tempstr);
					if(model.getType().getId().intValue()==8&&model.getInvest()!=null&&model.getInvest()>0) {
						tempstr = model.getInvest()+"万美元";
						this.createCell(row1, 4, styleC, "s",tempstr);
					}else{
						this.createCell(row1, 4, styleC, "l", model.getInvest());
					}
					this.createCell(row1, 5, styleC, "d", model.getArea());
					this.createCell(row1, 6, styleC, "d", model.getAgrarea());
					this.createCell(row1, 7, styleC, "d", model.getFarmingarea());
					if (detaili == 0) {
						this.createCell(row1, 8, styleC, "s", StringUtils.isNotBlank(model.getJys()) ? "√" : "");
						this.createCell(row1, 9, styleC, "s", StringUtils.isNotBlank(model.getKy()) ? "√" : "");
						this.createCell(row1, 10, styleC, "s", StringUtils.isNotBlank(model.getSj()) ? "√" : "");
						this.createCell(row1, 11, styleC, "s", StringUtils.isNotBlank(model.getXz()) ? "√" : "");
						this.createCell(row1, 12, styleC, "s", StringUtils.isNotBlank(model.getYdxk()) ? "√" : "");
						this.createCell(row1, 13, styleC, "s", StringUtils.isNotBlank(model.getYdys()) ? "√" : "");
					} else {
						this.createCell(row1, 8, styleL, "s", model.getJys());
						this.createCell(row1, 9, styleL, "s", model.getKy());
						this.createCell(row1, 10, styleL, "s", model.getSj());
						this.createCell(row1, 11, styleL, "s", model.getXz());
						this.createCell(row1, 12, styleL, "s", model.getYdxk());
						this.createCell(row1, 13, styleL, "s", model.getYdys());
					}

					this.createCell(row1, 14, styleL, "s", model.getDutycomp().getName());

					tempstr = (model.getBb() != null && model.getBb() == 1) ? "报部": "";
					if (model.getNote() != null) tempstr += model.getNote();
					this.createCell(row1, 15, styleL, "s", tempstr);

					lasttid = modeltype.getTid();
				}
				// 最后一个type
				if (typerow != null) {
					this.createCell(typerow, 2, sumsstyle, "s", lasttypename+ "（" + sum + "个项目）");
					sheet.addMergedRegion(new CellRangeAddress(typerow.getRowNum(), typerow.getRowNum(), 2, 3));
					sumproj += sum;
					typerow.getCell(6).setCellValue(sumagrarea);
					sumprojagrarea += sumagrarea;
				}

				headrow3.getCell(2).setCellValue(new HSSFRichTextString("合计（" + sumproj + "个项目）"));
				headrow3.getCell(6).setCellValue(sumprojagrarea);
				sheet.addMergedRegion(new CellRangeAddress(headrow3.getRowNum(), headrow3.getRowNum(), 2, 3));

			} else {
				this.createCell(sheet.createRow(0), 0, styleC, "s", "查无资料");
			}
		} catch (Exception e) {
			e.printStackTrace();
		}

	}
	public void createAgrSheets(HSSFWorkbook workbook,int detaili,int year, String titlestr,
			List<Proj> dataList,int sheetno) throws Exception {

		HSSFSheet sheet = null;
		try {
			sheet = workbook.createSheet(titlestr!=null?titlestr:"项目");
			HSSFPrintSetup hps = sheet.getPrintSetup();
			hps.setPaperSize((short) 9); // 设置A4纸
			hps.setLandscape(true); // 将页面设置为横向打印模式
			hps.setScale((short) 85);
			sheet.setHorizontallyCenter(true); // 设置打印页面为水平居中
			sheet.setVerticallyCenter(true);
			HSSFFooter footer = sheet.getFooter();// 设置footer的位置和显示的内容
			footer.setRight("第 " + HSSFFooter.page() + " 页"); 
			sheet.setMargin(HSSFSheet.TopMargin,(double)0.6);  
			sheet.setMargin(HSSFSheet.BottomMargin,(double)0.6);  
			sheet.setMargin(HSSFSheet.LeftMargin,(double)0.6);  
			sheet.setMargin(HSSFSheet.RightMargin,(double)0.6);  
			hps.setHeaderMargin((double)0.5);  
			hps.setFooterMargin((double)0.5);  
			
			// 设置列宽
			sheet.setColumnWidth(0, 1200);
			sheet.setColumnWidth(1, 1200);
			sheet.setColumnWidth(2, 5000);		
			sheet.setColumnWidth(3, 4500);
			sheet.setColumnWidth(4, 3000);
			sheet.setColumnWidth(5, 3000);
			sheet.setColumnWidth(6, 3000);
			sheet.setColumnWidth(7, 3000);
			sheet.setColumnWidth(8, 2650);
			sheet.setColumnWidth(9, 2650);
			sheet.setColumnWidth(10, 2650);
			sheet.setColumnWidth(11, 2600);
			sheet.setColumnWidth(12, 3000);
			sheet.setColumnWidth(13, 3000);

			// 获取样式
			HSSFCellStyle titlestyle = this.createTitleStyle(workbook);
			HSSFCellStyle styleC = this.createCommonStyle(workbook,HSSFCellStyle.ALIGN_CENTER);
			HSSFCellStyle styleL = this.createCommonStyle(workbook,HSSFCellStyle.ALIGN_LEFT);
			HSSFCellStyle headstyle = this.createHeadStyle(workbook);
			HSSFCellStyle sumsstyle = this.createSumSStyle(workbook);

			//
			if (dataList != null && dataList.size() > 0) {

				// 建立title行
				HSSFRow rowtitle = sheet.createRow((short) 0);
				String tt ="杭州市";
				if(year>1999&&year<9999) tt+=year+"年";
				else if(year>10000) tt+=year/100+"年第"+intarray[year%100]+"批";
				tt+="市本级新增建设用地项目计划进度申报";
				if(titlestr!=null) tt+="（"+titlestr+"）";
				this.createTHCell(rowtitle, 0, titlestyle,tt);

				// 建立head行
				
				HSSFRow headrow1 = sheet.createRow((short) 1);
				HSSFRow headrow2 = sheet.createRow((short) 2);
				for (int i = 0; i < headargarray.length; i++) {
					if (i >= 8 && i <= 11) {
						this.createTHCell(headrow1, i, headstyle, "项目农转用上报进度");
						this.createTHCell(headrow2, i, headstyle,headargarray[i]);
					} else {
						this.createTHCell(headrow1, i, headstyle,headargarray[i]);
						this.createTHCell(headrow2, i, headstyle, "");
						sheet.addMergedRegion(new CellRangeAddress(1, 2, i, i));
					}
					sheet.addMergedRegion(new CellRangeAddress(1, 1, 8, 11));

				}
				sheet.addMergedRegion(new CellRangeAddress(0, 0, 0, 13));
				workbook.setRepeatingRowsAndColumns(sheetno,-1,-1,1,2);  

				
				// 给excel填充数据
				int lasttid = 0;
				int k = 4;
				HSSFRow typerow = null;
				HSSFRow typeprow = null;//父类别
				String lasttypename = "";String lasttypepname = "";
				
				Integer sum = new Integer(0);//分类总计
				Integer sump = new Integer(0);//父分类总计
				Integer sumproj = new Integer(0);//总计
	
				
				Double sumagrarea = new Double(0);Double sumpagrarea = new Double(0);
				Double sumprojagrarea = new Double(0);				


				// 合计项目个数
				HSSFRow headrow3 = sheet.createRow((short) 3);
				headrow3.setHeight((short) 400);
				String tempstr = "";
				for (int i = 0; i < dataList.size(); i++) {
					Proj model = (Proj) dataList.get(i);
					Type modeltype = model.getType();
					// 分类
					if (lasttid != modeltype.getTid()) {
						if (typerow != null) {// 上一typerow
							typerow.setHeight((short) 700);
							this.createCell(typerow, 2, sumsstyle, "s",lasttypename + "（" + sum + "个项目）");							
							
							sheet.addMergedRegion(new CellRangeAddress(typerow.getRowNum(), typerow.getRowNum(), 2, 3));

							sumproj += sum;
							sump += sum;
							sum = 0;
							
							
							typerow.getCell(6).setCellValue(sumagrarea);
							sumprojagrarea += sumagrarea;
							sumpagrarea += sumagrarea;
							sumagrarea = 0D;


						}
						if(modeltype.getTid()%100==1){  //出现子分类 需要加父分类行
							typeprow = sheet.createRow((short) (i + k));k++;
							typeprow.setHeight((short) 700);
							sump=0;;sumpagrarea=0D;
							Type tp = typeManager.getTypeByTid(modeltype.getTid()/100*100);
							lasttypepname = intarray[modeltype.getTid()/100]+ "、" +tp.getName();							
						}
						if(modeltype.getTid()%100==0){ //无子分类时 父分类行赋空
							if(typeprow!=null){
								this.createCell(typeprow, 2, sumsstyle, "s",lasttypepname + "（" + sump + "个项目）");
								sheet.addMergedRegion(new CellRangeAddress(typeprow.getRowNum(), typeprow.getRowNum(), 2, 3));			
								typeprow.getCell(6).setCellValue(sumpagrarea);
						
							}
							typeprow = null;
						}
						typerow = sheet.createRow((short) (i + k));// 创建此type row
						
						for (int j = 0; j < headargarray.length; j++) {
							if (i == 0) this.createCell(headrow3, j, sumsstyle, "s",null);// 总合计行各cell
							if (j != 2) this.createCell(typerow, j, sumsstyle, "s",null);// type合计行各cell
							if (modeltype.getTid()%100==1) this.createCell(typeprow, j, sumsstyle, "s",null);
						}
						
						lasttypename = (modeltype.getTid()%100!=0?intsarray[modeltype.getTid()%100]:intarray[modeltype.getTid() / 100])+ "、" + modeltype.getName();

						k++;
					}
					HSSFRow row1 = sheet.createRow((short) (i + k));// 建立新行
					sumagrarea += (model.getAgrarea() != null) ? model.getAgrarea() : 0;
					sum++;

					this.createCell(row1, 0, styleC, "i", i + 1);
					this.createCell(row1, 1, styleC, "s",(model.getZd() != null && model.getZd() == 1) ? "*": "");
					this.createCell(row1, 2, styleL, "s", model.getTitle());					
					tempstr = "";
					if (model.getContentlength() != null) tempstr += "长" + model.getContentlength() + "米 ";
					if (model.getContentwidth() != null) tempstr += "，宽" + model.getContentwidth() + "米 ";
					if (model.getContentarea() != null) tempstr += "建筑面积" + model.getContentarea() + "平方米 ";
					if (model.getContent() != null) tempstr += model.getContent();
					this.createCell(row1, 3, styleL, "s", tempstr);
					this.createCell(row1, 4, styleC, "l", model.getInvest());
					this.createCell(row1, 5, styleC, "d", model.getArea());
					this.createCell(row1, 6, styleC, "d", model.getAgrarea());
					this.createCell(row1, 7, styleC, "d", model.getFarmingarea());
					if (detaili == 0) {
						this.createCell(row1, 8, styleC, "s", (model.getCgtsb()!=null&&model.getCgtsb()==1) ? "√" : "");
						this.createCell(row1, 9, styleC, "s", (model.getCgtsj()!=null&&model.getCgtsj()==1) ? "√" : "");
						this.createCell(row1, 10, styleC, "s",(model.getPgtsj()!=null&&model.getPgtsj()==1) ? "√" : "");
						this.createCell(row1, 11, styleC, "s",(model.getSgtpf()!=null&&model.getSgtpf()==1) ? "√" : "");
					} else {
						this.createCell(row1, 8, styleC, "s", (model.getCgtsb()!=null&&model.getCgtsb()==1) ? "√" : "");
						this.createCell(row1, 9, styleC, "s", (model.getCgtsj()!=null&&model.getCgtsj()==1) ? "√" : "");
						this.createCell(row1, 10, styleC, "s",(model.getPgtsj()!=null&&model.getPgtsj()==1) ? "√" : "");
						this.createCell(row1, 11, styleC, "s",StringUtils.isNotBlank(model.getSgtpfwh())?model.getSgtpfwh():((model.getSgtpf()!=null&&model.getSgtpf()==1) ? "√" : ""));
					}

					this.createCell(row1, 12, styleL, "s", model.getDutycomp().getName());

					tempstr = (model.getBb() != null && model.getBb() == 1) ? "报部": "";
					if (model.getNote() != null) tempstr += model.getNote();
					this.createCell(row1, 13, styleL, "s", tempstr);

					lasttid = modeltype.getTid();
				}
				// 最后一个type
				if (typerow != null) {
					this.createCell(typerow, 2, sumsstyle, "s", lasttypename+ "（" + sum + "个项目）");
					sheet.addMergedRegion(new CellRangeAddress(typerow.getRowNum(), typerow.getRowNum(), 2, 3));
					sumproj += sum;
					typerow.getCell(6).setCellValue(sumagrarea);
					sumprojagrarea += sumagrarea;
				}

				headrow3.getCell(2).setCellValue(new HSSFRichTextString("合计（" + sumproj + "个项目）"));
				headrow3.getCell(6).setCellValue(sumprojagrarea);
				sheet.addMergedRegion(new CellRangeAddress(headrow3.getRowNum(), headrow3.getRowNum(), 2, 3));

			} else {
				this.createCell(sheet.createRow(0), 0, styleC, "s", "查无资料");
			}
		} catch (Exception e) {
			e.printStackTrace();
		}

	}
	
	private void setSheetColumnWidth(HSSFSheet sheet) {
		// 根据你数据里面的记录有多少列，就设置多少列
		sheet.setColumnWidth(0, 1200);
		sheet.setColumnWidth(1, 1200);
		sheet.setColumnWidth(2, 4300);		
		sheet.setColumnWidth(3, 4000);
		sheet.setColumnWidth(4, 2600);
		sheet.setColumnWidth(5, 2250);
		sheet.setColumnWidth(6, 2250);
		sheet.setColumnWidth(7, 2250);
		sheet.setColumnWidth(8, 2650);
		sheet.setColumnWidth(9, 2650);
		sheet.setColumnWidth(10, 2650);
		sheet.setColumnWidth(11, 2600);
		sheet.setColumnWidth(12, 2600);
		sheet.setColumnWidth(13, 2600);
		sheet.setColumnWidth(14, 2700);
		sheet.setColumnWidth(15, 2500);
		
	}

	// 设置excel的title样式
	private HSSFCellStyle createCommonStyle(HSSFWorkbook wb, short align) {
		HSSFFont boldFont = wb.createFont();
		boldFont.setFontName("仿宋_GB2312");
		boldFont.setFontHeightInPoints((short) 10);
		HSSFCellStyle style = wb.createCellStyle();
		style.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER); // 垂直居中
		style.setAlignment(align);
		style.setFont(boldFont);
		style.setDataFormat(HSSFDataFormat.getBuiltinFormat("#,##0.0"));
		style.setWrapText(true);
		style.setBorderBottom(HSSFCellStyle.BORDER_THIN);// 下边框
		style.setBorderLeft(HSSFCellStyle.BORDER_THIN);// 左边框
		style.setBorderRight(HSSFCellStyle.BORDER_THIN);// 右边框
		style.setBorderTop(HSSFCellStyle.BORDER_THIN);// 上边框

		return style;
	}

	private HSSFCellStyle createTitleStyle(HSSFWorkbook wb) {
		HSSFFont boldFont = wb.createFont();
		boldFont.setFontName("黑体");
		boldFont.setFontHeightInPoints((short) 20);
		boldFont.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);
		HSSFCellStyle style = wb.createCellStyle();
		style.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER); // 垂直居中
		style.setAlignment(HSSFCellStyle.ALIGN_CENTER);
		style.setFont(boldFont);
		return style;
	}

	private HSSFCellStyle createHeadStyle(HSSFWorkbook wb) {
		HSSFFont boldFont = wb.createFont();
		boldFont.setFontName("仿宋_GB2312");
		boldFont.setFontHeightInPoints((short) 10);
		boldFont.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);
		HSSFCellStyle style = wb.createCellStyle();
		style.setAlignment(HSSFCellStyle.ALIGN_CENTER);
		style.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);
		style.setFont(boldFont);
		style.setWrapText(true);
		style.setBorderBottom(HSSFCellStyle.BORDER_THIN);// 下边框
		style.setBorderLeft(HSSFCellStyle.BORDER_THIN);// 左边框
		style.setBorderRight(HSSFCellStyle.BORDER_THIN);// 右边框
		style.setBorderTop(HSSFCellStyle.BORDER_THIN);// 上边框

		return style;
	}

	private HSSFCellStyle createSumSStyle(HSSFWorkbook wb) {
		HSSFFont boldFont = wb.createFont();
		boldFont.setFontName("黑体");
		boldFont.setFontHeightInPoints((short) 10);
		boldFont.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);
		HSSFCellStyle style = wb.createCellStyle();
		style.setAlignment(HSSFCellStyle.ALIGN_CENTER);
		style.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);
		style.setFont(boldFont);
		style.setWrapText(true);
		style.setBorderBottom(HSSFCellStyle.BORDER_THIN);// 下边框
		style.setBorderLeft(HSSFCellStyle.BORDER_THIN);// 左边框
		style.setBorderRight(HSSFCellStyle.BORDER_THIN);// 右边框
		style.setBorderTop(HSSFCellStyle.BORDER_THIN);// 上边框

		return style;
	}
	// 创建标题单元格

	private void createTHCell(HSSFRow row, int column, HSSFCellStyle style,
			Object value) {
		HSSFCell cell = row.createCell(column);
		if (style != null) {
			cell.setCellStyle(style);
		}
		cell.setCellValue(new HSSFRichTextString(value.toString()));

	}

	// 创建Excel单元格

	private void createCell(HSSFRow row, int column, HSSFCellStyle style,
			String cellType, Object value) {
		HSSFCell cell = row.createCell(column);

		// cell.setEncoding(HSSFCell.ENCODING_UTF_16);
		if (style != null) {
			cell.setCellStyle(style);
		}
		if (value != null) {
			if (cellType.equals("d")) {
				style.setDataFormat(HSSFDataFormat.getBuiltinFormat("###,##0.0"));
				cell.setCellType(HSSFCell.CELL_TYPE_NUMERIC);
				cell.setCellValue((Double) value);
			} else if (cellType.equals("i")) {
				cell.setCellType(HSSFCell.CELL_TYPE_NUMERIC);
				cell.setCellValue((Integer) value);
			} else if (cellType.equals("l")) {
				cell.setCellType(HSSFCell.CELL_TYPE_NUMERIC);
				cell.setCellValue((Long) value);
			}else if (cellType.equals("s")) {
				cell.setCellType(HSSFCell.CELL_TYPE_STRING);
				cell.setCellValue(new HSSFRichTextString(value.toString()));
			}
		}

	}

}
